﻿using System;
using System.Configuration;
using System.Data.SqlClient;
using System.Reflection;
using log4net;
using System.Data;

namespace AkazoNobel.Scheduler.StoreMasterData.DataAccess
{
    public static class SqlConnectionHelper
    {

        /// <summary>
        /// 日志记录
        /// </summary>
        private static readonly ILog Logger = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);

        /// <summary>
        /// 根据数据库连接名称获取SqlConnection
        /// </summary>
        /// <param name="name">App.config文件中的链接名称</param>
        /// <returns>SqlConnection</returns>
        public static SqlConnection GetConnection(string name)
        {
            SqlConnection connection = null;
            try
            {
                var connStr = ConfigurationManager.ConnectionStrings[name].ConnectionString;
                connection = new SqlConnection(connStr);
                connection.Open();
            }
            catch (Exception e)
            {
                Logger.Error(e.Message);
                
            }

            return connection;
        }

        /// <summary>
        /// 返回数据集存储过程
        /// </summary>
        /// <param name="storedProcName"></param>
        /// <param name="parameters"></param>
        /// <param name="tableName"></param>
        /// <param name="Times"></param>
        /// <param name="connectionName"></param>
        /// <returns></returns>
        public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, int Times, string connectionName)
        {
            using (var connection = GetConnection(connectionName))
            {
                DataSet dataSet = new DataSet();
                SqlDataAdapter sqlDA = new SqlDataAdapter();
                sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
                sqlDA.SelectCommand.CommandTimeout = Times;
                sqlDA.Fill(dataSet);
                connection.Close();
                return dataSet;
            }
        }


        /// <summary>
        /// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)
        /// </summary>
        /// <param name="connection">数据库连接</param>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>SqlCommand</returns>
        private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
        {
            SqlCommand command = new SqlCommand(storedProcName, connection);
            command.CommandType = CommandType.StoredProcedure;
            foreach (SqlParameter parameter in parameters)
            {
                if (parameter != null)
                {
                    // 检查未分配值的输出参数,将其分配以DBNull.Value.
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                        (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    command.Parameters.Add(parameter);
                }
            }

            return command;
        }


    }
}
